# coding: utf-8

#  NOTE by ld_wu
# 数据库中尚未绑定微信手机号的工程商用户修改为营销用户
# sms_marketing_obj = true

import os
import set_env_path  # 请勿删除

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
Session = sessionmaker(bind=engine)
session = Session()


def update_sms_marketing_obj():
    """
    create by ld_wu 2017-10-30
    更新尚未绑定微信手机号的工程商用户为营销用户
    """
    # TODO by ld_wu 2018-04-24 排除手动拉新用户
    update_sql = """
        update 
            user 
        set sms_marketing_obj=1
        where
            mobile like '1%' 
            and type in (99, 100)
            and bind_wx_openid=0
            and sms_marketing_obj=0
            and black = 0
            and is_internal = 0
            and deleted_at = 0
            and is_virt=0
            and id not in {}
    """

    # 付过费、抢过单
    excluded_sql = """
        select user.id
        from user
        where 
             wx_openid ='' 
             and deleted_at=0 
             and mobile like '1%' 
             and is_internal=0 
             and is_virt=0
             and 
             (
                 id in (
                     select
                          DISTINCT supplier_id 
                     from partic
                     where 
                          deleted_at=0 
                          and status not in (0, 300) 
                          and created_at> 1500048000
                  ) or
                 id in (
                     select DISTINCT uid 
                     from bill 
                     where 
                           status=200 
                           and type=1 
                           and sub_type in (2, 3) 
                           and deleted_at=0 
                           and created_at > 1500048000
                 )
             )
    """

    res = session.execute(
        excluded_sql
    ).fetchall()

    excluded_ids = tuple([each[0] for each in res])
    print(excluded_ids)

    session.execute(
        update_sql.format(excluded_ids)
    )

    session.commit()

if __name__ == '__main__':
    update_sms_marketing_obj()
